﻿
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Projects]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Projects](
	[ProjectID] [int] IDENTITY(1,1) NOT NULL,
	[ProjectName] [nvarchar](255) NOT NULL,
	[ProjectDescription] [nvarchar](2048) NULL,
 CONSTRAINT [PK_Projects] PRIMARY KEY CLUSTERED 
(
	[ProjectID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_Projects] UNIQUE NONCLUSTERED 
(
	[ProjectName] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[crosstab]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[crosstab] 
@select varchar(8000),
@sumfunc varchar(100), 
@pvt varchar(100), 
@table varchar(100),
@ProjectIdString as varchar(50),
@SolutionIdString as varchar(50)        

AS

DECLARE @sql varchar(8000), @delim varchar(1)

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

	EXEC (''SELECT '' + @pvt + '' AS pvt INTO ##pvt FROM '' + @table + '' WHERE 1=2'')
	EXEC (''INSERT INTO ##pvt SELECT DISTINCT '' + @pvt + '' FROM '' + @table + '' WHERE '' + @pvt + '' Is Not Null And ProjectID = '' + @ProjectIdString + '' And SolutionID = '' + @SolutionIdString)

	SELECT @sql='''',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, '' END)'' )
	SELECT @delim=CASE Sign( CharIndex(''char'', data_type)+CharIndex(''date'', data_type) ) WHEN 0 THEN '''' ELSE '''''''' END 
	FROM tempdb.information_schema.columns 
	WHERE table_name=''##pvt'' AND column_name=''pvt''

	SELECT @sql=@sql + '''''''' + convert(varchar(100), pvt) + '''''' = '' + stuff(@sumfunc,charindex( ''('', @sumfunc )+1, 0, '' CASE '' + @pvt + '' WHEN '' + @delim + convert(varchar(100), pvt) + @delim + '' THEN '' ) + '', '' FROM ##pvt
	DROP TABLE ##pvt

	SELECT @sql=left(@sql, len(@sql)-1)
	SELECT @select=stuff(@select, charindex('' FROM '', @select)+1, 0, '', '' + @sql + '' '')
	EXEC (@select)
SET ANSI_WARNINGS ON
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Environments]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Environments](
	[EnvironmentID] [int] IDENTITY(1,1) NOT NULL,
	[EnvironmentName] [nvarchar](255) NOT NULL,
	[EnvironmentDEscription] [nvarchar](2048) NULL,
	[ProjectID] [int] NOT NULL,
 CONSTRAINT [PK_Environments] PRIMARY KEY CLUSTERED 
(
	[EnvironmentID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_Environments] UNIQUE NONCLUSTERED 
(
	[EnvironmentName] ASC,
	[ProjectID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Solutions]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Solutions](
	[SolutionID] [int] IDENTITY(1,1) NOT NULL,
	[SolutionName] [nvarchar](256) NOT NULL,
	[SolutionDescription] [nvarchar](2048) NULL,
	[ProjectID] [int] NOT NULL,
 CONSTRAINT [PK_Solutions] PRIMARY KEY CLUSTERED 
(
	[SolutionID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_Solutions] UNIQUE NONCLUSTERED 
(
	[ProjectID] ASC,
	[SolutionName] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SettingValues]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SettingValues](
	[SettingValueID] [int] IDENTITY(1,1) NOT NULL,
	[SolutionSettingID] [int] NOT NULL,
	[EnvironmentID] [int] NOT NULL,
	[SettingValue] [nvarchar](512) NULL,
 CONSTRAINT [PK_SettingValues] PRIMARY KEY CLUSTERED 
(
	[SettingValueID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SolutionSettings]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SolutionSettings](
	[SolutionSettingID] [int] IDENTITY(1,1) NOT NULL,
	[SolutionID] [int] NOT NULL,
	[SettingTag] [nvarchar](255) NOT NULL,
	[DefaultValue] [nvarchar](512) NULL,
	[SettingDescription] [nvarchar](2048) NULL,
 CONSTRAINT [PK_SolutionSettings] PRIMARY KEY CLUSTERED 
(
	[SolutionSettingID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_SolutionSettings] UNIQUE NONCLUSTERED 
(
	[SolutionID] ASC,
	[SettingTag] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FullConfigurationDetails]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[FullConfigurationDetails]
AS
SELECT     dbo.Projects.ProjectID, dbo.Projects.ProjectName, dbo.Solutions.SolutionID, dbo.Solutions.SolutionName, dbo.SolutionSettings.SolutionSettingID, 
                      dbo.SolutionSettings.SettingTag, dbo.SolutionSettings.DefaultValue, dbo.Environments.EnvironmentID, dbo.Environments.EnvironmentName, 
                      dbo.SettingValues.SettingValueID, dbo.SettingValues.SettingValue
FROM         dbo.SolutionSettings INNER JOIN
                      dbo.Environments INNER JOIN
                      dbo.Projects ON dbo.Environments.ProjectID = dbo.Projects.ProjectID INNER JOIN
                      dbo.Solutions ON dbo.Projects.ProjectID = dbo.Solutions.ProjectID ON dbo.SolutionSettings.SolutionID = dbo.Solutions.SolutionID LEFT OUTER JOIN
                      dbo.SettingValues ON dbo.Environments.EnvironmentID = dbo.SettingValues.EnvironmentID AND 
                      dbo.SolutionSettings.SolutionSettingID = dbo.SettingValues.SolutionSettingID
' 
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetConfigurationCrossTab]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GetConfigurationCrossTab]
	@ProjectId int,

        @SolutionId int 

AS

BEGIN

        -- SET NOCOUNT ON added to prevent extra result sets from

        -- interfering with SELECT statements.

        SET NOCOUNT ON;

        Declare @ProjectIdString as varchar(50)
        Declare @SolutionIdString as varchar(50)

        Select @ProjectIdString = Cast(@ProjectId as varchar)
        Select @SolutionIdString = Cast(@SolutionId as varchar)

		Declare @Sql nvarchar (2000)
        Set @Sql = ''SELECT SettingTag as [Tag] FROM FullConfigurationDetails Where ProjectID = '' + @ProjectIdString + '' And SolutionID = '' + @SolutionIdString + '' Group By SettingTag''
        Execute crosstab @Sql, ''max([SettingValue])'', ''EnvironmentName'', ''FullConfigurationDetails'', @ProjectIdString, @SolutionIdString
End
' 
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Environments_Projects]') AND parent_object_id = OBJECT_ID(N'[dbo].[Environments]'))
ALTER TABLE [dbo].[Environments]  WITH CHECK ADD  CONSTRAINT [FK_Environments_Projects] FOREIGN KEY([ProjectID])
REFERENCES [dbo].[Projects] ([ProjectID])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Solutions_Projects]') AND parent_object_id = OBJECT_ID(N'[dbo].[Solutions]'))
ALTER TABLE [dbo].[Solutions]  WITH CHECK ADD  CONSTRAINT [FK_Solutions_Projects] FOREIGN KEY([ProjectID])
REFERENCES [dbo].[Projects] ([ProjectID])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_SettingValues_Environments]') AND parent_object_id = OBJECT_ID(N'[dbo].[SettingValues]'))
ALTER TABLE [dbo].[SettingValues]  WITH CHECK ADD  CONSTRAINT [FK_SettingValues_Environments] FOREIGN KEY([EnvironmentID])
REFERENCES [dbo].[Environments] ([EnvironmentID])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_SettingValues_SolutionSettings]') AND parent_object_id = OBJECT_ID(N'[dbo].[SettingValues]'))
ALTER TABLE [dbo].[SettingValues]  WITH CHECK ADD  CONSTRAINT [FK_SettingValues_SolutionSettings] FOREIGN KEY([SolutionSettingID])
REFERENCES [dbo].[SolutionSettings] ([SolutionSettingID])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_SolutionSettings_Solutions]') AND parent_object_id = OBJECT_ID(N'[dbo].[SolutionSettings]'))
ALTER TABLE [dbo].[SolutionSettings]  WITH CHECK ADD  CONSTRAINT [FK_SolutionSettings_Solutions] FOREIGN KEY([SolutionID])
REFERENCES [dbo].[Solutions] ([SolutionID])




-- Add Audit Tables
-- ========================================

/****** Object:  Table [dbo].[SolutionSettingChanges]    Script Date: 08/23/2011 13:08:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SolutionSettingChanges](
	[ChangeID] [int] IDENTITY(1,1) NOT NULL,
	[ProjectID] [int] NOT NULL,
	[ProjectName] [nvarchar](255) NOT NULL,
	[SolutionID] [int] NOT NULL,
	[SolutionName] [nvarchar](255) NOT NULL,
	[SolutionSettingID] [int] NOT NULL,
	[SolutionSettingTag] [nvarchar](255) NOT NULL,
	[SolutionSettingDefaultValue] [nvarchar](512) NULL,
	[SolutionSettingDescription] [nvarchar](2048) NULL,
	[ChangedBy] [nvarchar](255) NOT NULL,
	[ChangedDateTime] [datetime] NOT NULL,
	[ChangeAction] [nvarchar] (50) NOT NULL
 CONSTRAINT [PK_SolutionSettingChanges] PRIMARY KEY CLUSTERED 
(
	[ChangeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[SolutionSettingChanges] ADD  CONSTRAINT [DF_SolutionSettingChanges_ChangedDateTime]  DEFAULT (getdate()) FOR [ChangedDateTime]
GO


DROP TRIGGER SolutionSettingsAudit
GO

CREATE TRIGGER SolutionSettingsAudit 
    ON dbo.SolutionSettings 
    FOR INSERT, UPDATE, DELETE 
AS 
	Declare
		@ProjectID int,
		@ProjectName nvarchar(255),
		@SolutionID int,
		@SolutionName nvarchar(255),
		@SolutionSettingID int,
		@SolutionSettingTag nvarchar(512),
		@SolutionSettingDescription nvarchar(2048),
		@SolutionSettingDefaultValue nvarchar(512),
		@ChangedBy nvarchar(50),
		@ChangeDateTime datetime,
		@Action nvarchar(50)

	Set @ChangeDateTime = GETDATE()
	Set @ChangedBy = SUSER_NAME()
	
    IF (SELECT COUNT(*) FROM inserted) > 0 
    BEGIN 
        IF (SELECT COUNT(*) FROM deleted) > 0 
        BEGIN 
			-- Get Parameters
			SELECT  
				@Action = 'Update',
				@SolutionID = SolutionID,
				@SolutionSettingID = SolutionSettingID,
				@SolutionSettingTag = SettingTag,
				@SolutionSettingDescription = SettingDescription,
				@SolutionSettingDefaultValue = DefaultValue
            FROM 
				inserted  
        END 
        ELSE 
        BEGIN 
			-- Get Parameters
			SELECT  
				@Action = 'Insert',
				@SolutionID = SolutionID,
				@SolutionSettingID = SolutionSettingID,
				@SolutionSettingTag = SettingTag,
				@SolutionSettingDescription = SettingDescription,
				@SolutionSettingDefaultValue = DefaultValue
            FROM 
				inserted 
        END 
    END 
    ELSE 
    BEGIN 
		-- Get Parameters
			SELECT  
				@Action = 'Deleted',
				@SolutionID = SolutionID,
				@SolutionSettingID = SolutionSettingID,
				@SolutionSettingTag = SettingTag,
				@SolutionSettingDescription = SettingDescription,
				@SolutionSettingDefaultValue = DefaultValue
            FROM 
				deleted 
    END 
    
    -- Get Additional Data
    Select 
		@SolutionName = SolutionName,
		@ProjectID = ProjectID  
	From Solutions 
	Where SolutionID = @SolutionID
	
	Select 
		@ProjectName = ProjectName
	From Projects 
	Where ProjectID = @ProjectID
    
    -- Add to audit
    INSERT INTO [SolutionSettingChanges]
			(
				[ProjectID]
			   ,[ProjectName]
			   ,[SolutionID]
			   ,[SolutionName]
			   ,[SolutionSettingID]
			   ,[SolutionSettingTag]
			   ,[SolutionSettingDefaultValue]
			   ,[SolutionSettingDescription]
			   ,[ChangedBy]
			   ,[ChangedDateTime]
			   ,[ChangeAction]
			)
			VALUES
           (
				@ProjectID
			   ,@ProjectName
			   ,@SolutionID
			   ,@SolutionName
			   ,@SolutionSettingID
			   ,@SolutionSettingTag
			   ,@SolutionSettingDefaultValue
			   ,@SolutionSettingDescription
			   ,@ChangedBy
			   ,@ChangeDateTime
			   ,@Action
			) 
GO


/****** Object:  Table [dbo].[SolutionSettingChanges]    Script Date: 08/23/2011 13:08:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SettingValuesChanges](
	[ChangeID] [int] IDENTITY(1,1) NOT NULL,
	[ProjectID] [int] NOT NULL,
	[ProjectName] [nvarchar](255) NOT NULL,
	[SolutionID] [int] NOT NULL,
	[SolutionName] [nvarchar](255) NOT NULL,
	[SolutionSettingID] [int] NOT NULL,
	[SolutionSettingTag] [nvarchar](255) NOT NULL,
	[EnvironmentID] [int] NOT Null,
	[EnvironmentName] [nvarchar](255) NOT NULL,
	[ChangedBy] [nvarchar](255) NOT NULL,
	[ChangedDateTime] [datetime] NOT NULL,
	[SettingValue] [nvarchar] (512) NOT NULL,
	[ChangeAction] [nvarchar] (50) NOT NULL,
CONSTRAINT [PK_SettingValuesChanges] PRIMARY KEY CLUSTERED 
(
	[ChangeID] ASC
)
WITH 
(
	PAD_INDEX  = OFF, 
	STATISTICS_NORECOMPUTE  = OFF, 
	IGNORE_DUP_KEY = OFF, 
	ALLOW_ROW_LOCKS  = ON, 
	ALLOW_PAGE_LOCKS  = ON
) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[SettingValuesChanges] ADD  CONSTRAINT [DF_SettingValuesChanges_ChangedDateTime]  DEFAULT (getdate()) FOR [ChangedDateTime]
GO

--Audit Trigger

DROP TRIGGER SettingValuesAudit
GO

CREATE TRIGGER SettingValuesAudit 
    ON dbo.SettingValues 
    FOR INSERT, UPDATE, DELETE 
AS 
	Declare
		@SettingValueID int,
		@ProjectID int,
		@ProjectName nvarchar(255),
		@SolutionID int,
		@SolutionName nvarchar(255),
		@SolutionSettingID int,
		@SolutionSettingTag nvarchar(512),
		@EnvironmentID int,
		@EnvironmentName nvarchar(255),
		@ChangedBy nvarchar(50),
		@ChangeDateTime datetime,
		@Action nvarchar(50),
		@SettingValue nvarchar(512)

	Set @ChangeDateTime = GETDATE()
	Set @ChangedBy = SUSER_NAME()
	
    IF (SELECT COUNT(*) FROM inserted) > 0 
    BEGIN 
        IF (SELECT COUNT(*) FROM deleted) > 0 
        BEGIN 
          -- Get Parameters
			SELECT  
				@Action = 'Updated',
				@SettingValueID = SettingValueID,
				@SolutionSettingID = SolutionSettingID,
				@EnvironmentID = EnvironmentID,
				@SettingValue = SettingValue
            FROM 
				inserted
        END 
        ELSE 
        BEGIN 
			-- Get Parameters
			SELECT  
				@Action = 'Inserted',
				@SettingValueID = SettingValueID,
				@SolutionSettingID = SolutionSettingID,
				@EnvironmentID = EnvironmentID,
				@SettingValue = SettingValue
            FROM 
				inserted 
        END 
    END 
    ELSE 
    BEGIN 
		 -- Get Parameters
			SELECT  
				@Action = 'Deleted',
				@SettingValueID = SettingValueID,
				@SolutionSettingID = SolutionSettingID,
				@EnvironmentID = EnvironmentID,
				@SettingValue = SettingValue
            FROM 
				deleted
    END 
    
    -- Get Additional Data
    Select
		@SolutionSettingTag = SettingTag,
		@SolutionID = SolutionID
	From SolutionSettings 
	Where SolutionSettingID = @SolutionSettingID 
	
    Select 
		@SolutionName = SolutionName,
		@ProjectID = ProjectID  
	From Solutions 
	Where SolutionID = @SolutionID
	
	Select 
		@ProjectName = ProjectName
	From Projects 
	Where ProjectID = @ProjectID
	
	Select 
		@EnvironmentName = EnvironmentName
	From Environments
	Where EnvironmentID = @EnvironmentID
			
	-- Add Audit Record
    INSERT INTO [SettingValuesChanges]
			   ([ProjectID]
			   ,[ProjectName]
			   ,[SolutionID]
			   ,[SolutionName]
			   ,[SolutionSettingID]
			   ,[SolutionSettingTag]
			   ,[EnvironmentID]
			   ,[EnvironmentName]
			   ,[ChangedBy]
			   ,[ChangedDateTime]
			   ,[ChangeAction]
			   ,[SettingValue])
			VALUES
			   (
			   @ProjectID
			   ,@ProjectName
			   ,@SolutionID
			   ,@SolutionName 
			   ,@SolutionSettingID
			   ,@SolutionSettingTag
			   ,@EnvironmentID
			   ,@EnvironmentName
			   ,@ChangedBy
			   ,@ChangeDateTime
			   ,@Action
			   ,@SettingValue
			   )
GO




